package com.ship.dispatch.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ship.dispatch.bean.SpYearPlanScheme;
import com.ship.dispatch.bean.SpYearPlanSchemeDetail;
import com.ship.dispatch.bean.SpYearlyPlan;
import com.ship.dispatch.model.dto.SchemeChartSimpleDto;
import com.ship.dispatch.util.SpYearlyPlanProvider;
import com.ship.dispatch.vo.SpDispatchSchemeDetailExVo;
import com.ship.dispatch.vo.SpYearPlanSchemeDetailVO;
import com.ship.dispatch.vo.SpYearlyPlanVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;

/**
 * <p>
 * 方案详情 Mapper 接口
 * </p>
 *
 * @author AbyssRabbit@163.com
 * @since 2023-10-17
 */
public interface SpYearPlanSchemeDetailMapper extends BaseMapper<SpYearPlanSchemeDetail> {
    /**
     * 船舶名称集合
     *
     * @return
     */
    @Select("select \n" +
            "d.ship_name\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.scheme_id = #{schemeId} \n" +
            "GROUP BY d.ship_name ")
    List<String> getPlanDetailsByShipName(@Param("schemeId") Long schemeId);

    /**
     * 货源名称集合
     *
     * @return
     */
    @Select("select \n" +
            "d.goods_num\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.scheme_id = #{schemeId} \n" +
            "GROUP BY d.goods_num")
    List<String> getPlanDetailsByGoodsName(@Param("schemeId") Long schemeId);

    /**
     * 资源方集合
     *
     * @return
     */
    @Select("select \n" +
            "d.seller\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where d.scheme_id = #{schemeId} \n" +
            "GROUP BY d.seller")
    List<String> getPlanDetailsBySellerName(@Param("schemeId") Long schemeId);

    @Select("select \n" +
            "d.*\n" +
            "from sp_year_plan_scheme s\n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id and p.state in ( ${state} )\n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and d.scheme_id = s.id and s.create_by = d.create_by\n" +
            "where s.id = #{schemeId} and ship_name = #{shipName}")
    List<SpYearPlanSchemeDetailVO> getPlanDetailsByShipNameAndSchemeId(@Param("schemeId") Long schemeId,@Param("shipName") String shipName,@Param("state") String state);

    @Select("select \n" +
            "d.*\n" +
            "from sp_year_plan_scheme s\n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id and p.state in ( ${state} )\n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and d.scheme_id = s.id and s.create_by = d.create_by\n" +
            "where s.id = #{schemeId} and goods_num = #{goodsNum}")
    List<SpYearPlanSchemeDetailVO> getPlanDetailsByGoodsNumAndSchemeId(@Param("schemeId") Long schemeId,@Param("goodsNum") String goodsNum,@Param("state") String state);

    @Select("select \n" +
            "d.*\n" +
            "from sp_year_plan_scheme s\n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id and p.state in ( ${state} )\n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and d.scheme_id = s.id and s.create_by = d.create_by\n" +
            "where s.id = #{schemeId} and seller = #{seller}")
    List<SpYearPlanSchemeDetailVO> getPlanDetailsBySellerAndSchemeId(@Param("schemeId") Long schemeId,@Param("seller") String seller,@Param("state") String state);

    @Select("select * from sp_year_plan_scheme where create_by = #{createBy} ORDER BY create_date desc limit 1")
    SpYearPlanScheme getPlanSchemeNewOne(@Param("createBy") String createBy);

    @Select("select * from sp_year_plan_scheme where create_by = #{createBy} ORDER BY create_date desc limit 1")
    SpYearPlanScheme getPlanSchemeNewByReduceOne(@Param("createBy") String createBy);

    /**
     * 船舶名称集合
     *
     * @return
     */
    @Select("select \n" +
            "d.ship_name\n" +
            "from sp_year_plan_scheme s \n" +
            "LEFT JOIN sp_yearly_plan p on s.plan_id = p.id \n" +
            "Left JOIN sp_year_plan_scheme_detail d on d.plan_id = p.id and s.create_by = d.create_by and d.scheme_id = s.id\n" +
            "where p.`year` = #{year} and s.create_by = #{createBy}  \n" +
            "GROUP BY d.ship_name")
    List<String> getPlanDetailsByShipNameCreateBy(@Param("year") Integer year,@Param("createBy") String createBy);

    /**
     * 获取导出列(年度计划)
     * @return
     */
    @Select("SELECT column_key FROM table_config WHERE `module` = #{tableName};")
    List<String> getTableColumnList(@Param(value = "tableName") String tableName);

    /**
     * 动态sql查询(年度计划)
     * @return
     */
    @SelectProvider(type = SpYearlyPlanProvider.class, method = "getYearlyPlanByColumnKeys")
    List<SpYearlyPlanVo> getYearlyPlanList(@Param("columnKeys") List<String> columnKeys);


    List<SpYearPlanSchemeDetail> getSchemeDetailHistory(@Param(value = "dto") SchemeChartSimpleDto dto);

    @Select("select * from sp_year_plan_scheme_detail where  scheme_number is not null and create_by = #{createBy} and plan_id = #{planId} ORDER BY create_date desc limit 1")
    SpYearPlanSchemeDetail selectMaxSchemeDetail(@Param(value = "createBy") String createBy,@Param(value = "planId") Long planId);

    /**
     * 获取导出列(调度优化)
     * @return
     */
    @Select("SELECT column_key FROM table_config WHERE `module` = 'sp_dispatch_scheme_detail';")
    List<String> getDispatchTableColumnList();

    @SelectProvider(type = SpYearlyPlanProvider.class, method = "getDispatchSchemeByColumnKeys")
    List<SpDispatchSchemeDetailExVo> getDispatchPlanList(@Param("columnKeys") List<String> columnKeys);
}